package org.example;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class dbTest {
    private static final String url = "jdbc:mysql://localhost:3306/test";
    private static final String user = "root";
    private static final String password = "123456";
    private static final String driver = "com.mysql.cj.jdbc.Driver";
    private static Connection conn = null;

    static void tableTest() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        // 修正：添加完整的表结构
        String sql = "CREATE TABLE IF NOT EXISTS teacher (" +
                "id INT PRIMARY KEY, " +
                "name VARCHAR(50) NOT NULL, " +
                "trs VARCHAR(50), " +
                "birthday DATE)";
        try (PreparedStatement pre = conn.prepareStatement(sql)) {
            pre.execute();
            System.out.println("The test table has been created or already exists");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void add(String name, int id, String trs, String birthday) {
        String sql = "INSERT INTO teacher(name, id, trs, birthday) VALUES(?, ?, ?, ?)";

        try (PreparedStatement pre = conn.prepareStatement(sql)) {
            pre.setString(1, name);
            pre.setInt(2, id);
            pre.setString(3, trs);
            // 修正：使用Date类型或者保持String类型一致
            pre.setDate(4, Date.valueOf(birthday)); // 将字符串转换为SQL Date

            int afRows = pre.executeUpdate();
            if (afRows > 0) {
                System.out.println("Record added successfully: " + name);
            }
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        }
    }

    public static void listAll() {
        String sql = "SELECT * FROM teacher";
        List<teacher> teachers = new ArrayList<>(); // 修正：变量名改为复数

        try (PreparedStatement pre = conn.prepareStatement(sql);
             ResultSet rs = pre.executeQuery()) {

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String trs = rs.getString("trs");
                Date birthday = rs.getDate("birthday");
                teachers.add(new teacher(id, name, trs, birthday));
            }

            if (teachers.isEmpty()) {
                System.out.println("No data found");
            } else {
                System.out.println("ID\tName\tTrs\tBirthday");
                System.out.println("----------------------------------------");
                for (teacher t : teachers) {
                    // 修正：输出格式与字段顺序匹配
                    System.out.printf("%d\t%s\t%s\t%s\n",
                            t.getId(), t.getName(), t.getTrs(), t.getBirthday());
                }
            }
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        }
    }

    // 添加更新方法
    public static void update() {
        String sql = "UPDATE teacher SET name = ?, trs =?, birthday = ? WHERE id = 1";

        try (PreparedStatement pre = conn.prepareStatement(sql)) {
            pre.setString(1, "songsolo");
            pre.setString(2, "ww213");
            pre.setDate(3, Date.valueOf("2003-11-21"));

            int affectedRows = pre.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Record with id=1 updated successfully");
            } else {
                System.out.println("No record found with id=1");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 添加删除方法
    public static void delete() {
       String sql = "DELETE FROM teacher WHERE id = 1";


        try (PreparedStatement pre = conn.prepareStatement(sql)) {
            int affectedRows = pre.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Record with id=1 deleted successfully");
            } else {
                System.out.println("No record found with id=1");
            }
            if (affectedRows == 0) {
                System.out.println("warning! there is no record which you want to delete!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    static class teacher {
        int id;
        String name;
        String trs;
        Date birthday;

        // 修正：初始化所有字段
        public teacher(int id, String name, String trs, Date birthday) {
            this.id = id;
            this.name = name;
            this.trs = trs;
            this.birthday = birthday;
        }

        public int getId() {
            return id;
        }

        public String getName() {
            return name;
        }

        public String getTrs() {
            return trs;
        }

        public Date getBirthday() {
            return birthday;
        }
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("MySQL connection established");
            tableTest();

            // 测试添加记录
          add("song", 1, "bw201", "2004-07-23");
          add("yuanfang", 2, "yf108", "2005-07-22");

            // 测试查询所有记录
            System.out.println("\n=== All records ===");
            listAll();

            // 测试更新id=1的记录
            System.out.println("\n=== Update record with id=1 ===");
            update();
            listAll();

            // 测试删除id=1的记录
            System.out.println("\n=== Delete record with id=1 ===");
            delete();
            listAll();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
                System.out.println("Database connection has been closed");
            }
        }
    }
}
/*已经实现
创建Java类CRUDTest类。
该类具有main()方法和使用PreparedStatement实现增加记录的方法add()、实现查询全部记录的方法listAll()、实现更新id=1的记录的方法update()、实现删除id=1的记录的方法delete()。
* */
